﻿using Ext.Net;
using NPOI.HSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;

/// <summary>
/// BasePage 的摘要说明
/// 修改时间2016-11-21。。。ssftest
/// </summary>
public class BasePage : System.Web.UI.Page
{
    #region 公共信息

    /// <summary>
    /// 设置Session
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="key"></param>
    /// <param name="val"></param>
    protected void SetSession<T>(string key, T val)
    {
        Session[key] = val;
    }

    /// <summary>
    /// 获取Session
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="key"></param>
    /// <returns></returns>
    protected T GetSession<T>(string key)
    {
        T val;
        if (Session[key] == null)
            val = default(T);
        else
            val = (T)Session[key];
        return val;
    }

    /// <summary>
    /// 登陆人ID
    /// </summary>
    public string BS_LoginUserID
    {
        get { return this.GetSession<string>("BasePage_BS_LoginUserID"); }
        set { this.SetSession<string>("BasePage_BS_LoginUserID", value); }
    }

    /// <summary>
    /// 登陆人名称
    /// </summary>
    public string BS_LoginUserName
    {
        get { return this.GetSession<string>("BasePage_BS_LoginUserName"); }
        set { this.SetSession<string>("BasePage_BS_LoginUserName", value); }
    }

    /// <summary>
    /// 登陆人部门名称
    /// </summary>
    public string BS_LoginDeptName
    {
        get { return this.GetSession<string>("BasePage_BS_LoginDeptName"); }
        set { this.SetSession<string>("BasePage_BS_LoginDeptName", value); }
    }

    /// <summary>
    /// 登陆人部门ID
    /// </summary>
    public string BS_LoginDeptId
    {
        get { return this.GetSession<string>("BasePage_BS_LoginDeptId"); }
        set { this.SetSession<string>("BasePage_BS_LoginDeptId", value); }
    }

    /// <summary>
    /// 登陆人DeptIndex
    /// </summary>
    public int BS_LoginDeptIndex
    {
        get { return this.GetSession<int>("BasePage_BS_LoginDeptIndex"); }
        set { this.SetSession<int>("BasePage_BS_LoginDeptIndex", value); }
    }

    /// <summary>
    /// 登陆人角色ID集合
    /// </summary>
    public List<string> BS_LoginRoles
    {
        get { return this.GetSession<List<string>>("BasePage_BS_LoginRoles"); }
        set { this.SetSession<List<string>>("BasePage_BS_LoginRoles", value); }
    }

    /// <summary>
    /// 登陆人是否拥有个人库房
    /// </summary>
    public int BS_PerStore
    {
        get { return this.GetSession<int>("BasePage_BS_PerStore"); }
        set { this.SetSession<int>("BasePage_BS_PerStore", value); }
    }

    /// <summary>
    /// 页面路径
    /// </summary>
    public string G_Url_allstring
    {
        get
        {
            return "http://" + Request.Url.Authority.ToString() + System.Web.HttpContext.Current.Request.ApplicationPath.ToString();
        }
    }

    #endregion

    public string G_FrameTabid
    {
        get
        {
            if (Request.QueryString["tabid"] != null)
            {
                return Request.QueryString["tabid"].ToString();
            }
            else
            {
                return "";
            }
        }
    }

    #region ViewState属性

    public string ViewState_OrderSre
    {
        get
        {
            return ViewState["ViewState_OrderSre"].ToString();
        }
        set { ViewState["ViewState_OrderSre"] = value; }
    }

    public string ViewState_ObjID
    {
        get
        {
            return ViewState["ViewState_ObjID"].ToString();
        }
        set { ViewState["ViewState_ObjID"] = value; }
    }

    public string ViewState_DetailId
    {
        get
        {
            return ViewState["ViewState_DetailId"].ToString();
        }
        set { ViewState["ViewState_DetailId"] = value; }
    }

    #endregion

    public BasePage()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }

    protected override void OnInit(EventArgs e)
    {
        //为空跳转至登陆页面
        if (string.IsNullOrEmpty(BS_LoginUserID) && Request.RawUrl != "/Login.aspx")
        {
            Response.Redirect("~/Login.aspx");
        }
        base.OnInit(e);
        Ext.Net.Button _extBtn_CheckInfo = this.FindControl("extBtn_CheckInfo") as Ext.Net.Button;
        if (_extBtn_CheckInfo != null)
        {
            _extBtn_CheckInfo.Visible = false;
            //判断是否有审核员角色
            if (BS_LoginRoles.Contains("02"))
            {
                if (Request.QueryString["ObjId"] != "Add")
                    _extBtn_CheckInfo.Visible = true;
            }
        }
    }



    #region 页面赋值取值函数

    public void SetFormLayout(Ext.Net.FormLayout frm_layout, DataTable Table)
    {
        string Control_name = "";
        if (Table.Rows.Count == 0)
            return;
        for (int i = 0; i < frm_layout.Controls.Count; i++)
        {
            if (frm_layout.Controls[i].ID == null)
                continue;
            Control_name = frm_layout.Controls[i].ID;
            if (Control_name.Length < 9)
                continue;
            if (Table.Columns.IndexOf(Control_name.Substring(8)) == -1)
                continue;
            SetControl(frm_layout, i, Control_name, Table.Rows[0][Control_name.Substring(8)].ToString(), 2);

        }
    }

    public void SetControl(object frm_lay, int i, string Control_name, string Control_value, int type)
    {
        string ControlType = Control_name.Substring(0, 8);
        Ext.Net.FormLayout lay;
        Ext.Net.FormPanel panel;
        if (type == 2)
        {
            lay = frm_lay as Ext.Net.FormLayout;

            if (ControlType == "exttxtf_")
            {
                (lay.Controls[i] as Ext.Net.TextField).Text = Control_value;
                return;
            }
            if (ControlType == "exttare_")
            {
                (lay.Controls[i] as Ext.Net.TextArea).Text = Control_value;
                return;
            }
            if (ControlType == "extcomb_")
            {
                (lay.Controls[i] as Ext.Net.ComboBox).SelectedItem.Value = Control_value;
                return;
            }
            if (ControlType == "extnumf_")
            {
                (lay.Controls[i] as Ext.Net.NumberField).Text = Control_value;
                return;
            }
            if (ControlType == "extdate_")
            {
                (lay.Controls[i] as Ext.Net.DateField).Value = Control_value;
                return;
            }
            if (ControlType == "exthidn_")
            {
                (lay.Controls[i] as Ext.Net.Hidden).Value = Control_value;
                return;
            }
            if (ControlType == "exttrig_")
            {
                (lay.Controls[i] as Ext.Net.TriggerField).Value = Control_value;
                return;
            }
        }
        else
        {
            panel = frm_lay as Ext.Net.FormPanel;
            if (ControlType == "exttxtf_")
            {
                (panel.Controls[0].Controls[i] as Ext.Net.TextField).Text = Control_value;
                return;
            }
            if (ControlType == "exttare_")
            {
                (panel.Controls[0].Controls[i] as Ext.Net.TextArea).Text = Control_value;
                return;
            }
            if (ControlType == "extcomb_")
            {
                (panel.Controls[0].Controls[i] as Ext.Net.ComboBox).SelectedItem.Value = Control_value;
                return;
            }
            if (ControlType == "extnumf_")
            {
                (panel.Controls[0].Controls[i] as Ext.Net.NumberField).Text = Control_value;
                return;
            }
            if (ControlType == "extdate_")
            {
                (panel.Controls[0].Controls[i] as Ext.Net.DateField).Value = Control_value;
                return;
            }
            if (ControlType == "exthidn_")
            {
                (panel.Controls[0].Controls[i] as Ext.Net.Hidden).Value = Control_value;
                return;
            }
            if (ControlType == "exttrig_")
            {
                (panel.Controls[0].Controls[i] as Ext.Net.TriggerField).Value = Control_value;
                return;
            }
        }



    }

    /// <summary>
    /// 查找所有控件
    /// </summary>
    public void SetContainer(ContainerBase con, DataTable dt)
    {
        if (con == null || dt.Rows.Count == 0)
            return;
        foreach (object obj in con.Items)
        {
            switch (obj.GetType().FullName)
            {
                case "Ext.Net.FieldSet":
                    SetContainer(obj as FieldSet, dt);
                    break;
                case "Ext.Net.Container":
                    SetContainer(obj as Container, dt);
                    break;
                default:
                    SetFieldValue(obj as Field, dt);
                    break;

            }
        }
    }

    /// <summary>
    /// 为控件赋值
    /// </summary>
    public void SetFieldValue(Field obj, DataTable Table)
    {
        string Control_name = "";
        if (obj == null || Table.Rows.Count == 0)
            return;
        Control_name = obj.ID;
        if (Control_name == null || Control_name.Length < 9)
            return;
        string clummName = Control_name.Substring(8);
        if (Table.Columns.IndexOf(clummName) == -1)
            return;
        obj.SetValue(Table.Rows[0][clummName].ToString());
    }

    #endregion

    /// <summary>
    /// 设置Container内部控件是否只读
    /// </summary>
    public void SetContainerReadOnly(ContainerBase con, bool isReadOnly)
    {
        if (con == null)
            return;
        foreach (object obj in con.Items)
        {
            switch (obj.GetType().FullName)
            {
                case "Ext.Net.Container":
                    SetContainerReadOnly(obj as Container, isReadOnly);
                    break;
                default:
                    var field = obj as Field;
                    field.ReadOnly = isReadOnly;
                    break;

            }
        }

    }



    /// <summary>
    /// 传入名称，获取拼音码
    /// </summary>
    public string GetPinyinByName(string name)
    {
        if (string.IsNullOrEmpty(name))
            return string.Empty;
        PinWbCode pin = new PinWbCode();
        return pin.GetPinyin(name.Trim());
    }

    //文本输入框特殊字符过滤
    public string FilterSpecChar(string strSource)
    {
        string[] specchar = {
                                "'"
                                //,"\""
                                //,"‘"
                                //,"’"
                                //,"“"
                                //,"”"
                                //,"＂"
                            };
        foreach (string objchar in specchar)
        {
            strSource = strSource.Replace(objchar, "");
        }
        return strSource;
    }

    /// <summary>
    /// 部门树
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void DeptNodeLoad(object sender, NodeLoadEventArgs e)
    {
        string CurDeptId = BS_LoginDeptId;
        //每当用户点击节点的时候 
        if (e.NodeID == "0")//绑定group
        {
            //清空节点信息
            e.Nodes.Clear();
            DataTable tmpDt = CommonLib.BLL.SYS_DEPT.GetListSub(" dept_id='00' and  del_flag =0 ", "dept_no").Tables[0];
            if (tmpDt.Rows.Count == 1)
            {
                Ext.Net.TreeNode root = new Ext.Net.TreeNode();
                root.Text = tmpDt.Rows[0]["dept_name"].ToString();
                root.NodeID = tmpDt.Rows[0]["dept_id"].ToString();
                root.Expanded = true;
                root.IconCls = "no-icon";
                if (tmpDt.Rows[0]["leaf"].ToString() == "0")
                    root.Leaf = true;
                root.Draggable = true;
                e.Nodes.Add(root);

                DataTable TreeData = CommonLib.BLL.SYS_DEPT.GetListSub(" del_flag =0 and rtrim(dept_id) like '" + tmpDt.Rows[0]["dept_id"].ToString().Trim() + "__' ", "dept_no").Tables[0];

                foreach (DataRow nodeRow in TreeData.Rows)
                {
                    AsyncTreeNode asyncNode = new AsyncTreeNode();
                    asyncNode.Text = nodeRow["dept_name"].ToString();
                    asyncNode.NodeID = nodeRow["dept_id"].ToString();
                    asyncNode.IconCls = "no-icon";
                    asyncNode.Leaf = nodeRow["leaf"].ToString() == "0";
                    if (CurDeptId != "")
                    {
                        string nodedDeptId = CurDeptId;
                        if (nodedDeptId.Trim().Length <= 4)
                        {
                            if (nodeRow["dept_id"].ToString() == nodedDeptId)
                                asyncNode.Expanded = true;
                        }
                        else
                        {
                            if (nodedDeptId.Trim().Length >= 4 && nodeRow["dept_id"].ToString().Trim() == nodedDeptId.Trim().Substring(0, 4))
                                asyncNode.Expanded = true;
                        }
                    }
                    root.Nodes.Add(asyncNode);
                }
            }
        }
        else
        {
            if (!string.IsNullOrEmpty(e.NodeID))
            {
                DataTable TreeData = CommonLib.BLL.SYS_DEPT.GetListSub(" del_flag =0 and rtrim(dept_id) like '" + e.NodeID.Trim() + "__' ", "dept_no").Tables[0];
                foreach (DataRow nodeRow in TreeData.Rows)
                {
                    AsyncTreeNode asyncNode = new AsyncTreeNode();
                    asyncNode.Text = nodeRow["dept_name"].ToString();
                    asyncNode.NodeID = nodeRow["dept_id"].ToString();
                    asyncNode.IconCls = "no-icon";
                    asyncNode.Leaf = !Convert.ToBoolean(nodeRow["nodeRow"]);
                    if (CurDeptId != "")
                    {
                        string nodedDeptId = CurDeptId;
                        if (nodedDeptId.Trim().Length <= 4)
                        {
                            asyncNode.Expanded = nodeRow["dept_id"].ToString() == nodedDeptId;
                        }
                        else
                        {
                            if (nodedDeptId.Trim().Length >= 6 && nodeRow["dept_id"].ToString().Trim() == nodedDeptId.Trim().Substring(0, 6))
                                asyncNode.Expanded = true;
                            else
                            {
                                if (nodedDeptId.Trim().Length >= 8 && nodeRow["dept_id"].ToString().Trim() == nodedDeptId.Trim().Substring(0, 8))
                                    asyncNode.Expanded = true;
                                else
                                {
                                    if (nodedDeptId.Trim().Length >= 10 && nodeRow["dept_id"].ToString().Trim() == nodedDeptId.Trim().Substring(0, 10))
                                        asyncNode.Expanded = true;
                                }
                            }
                        }
                    }
                    e.Nodes.Add(asyncNode);
                }
            }
        }
    }

    #region 分页公用部分

    public void BindGridPSData(StoreRefreshDataEventArgs e, string OrderStr, PagingToolbar PageBar, Store Sto, int RecCount)
    {
        e.Total = RecCount;
        string NewOrderStr = OrderStr;
        int PageSize = PageBar.PageSize; //获取当前在页面中PagingToolBar 的PageSize的值
        int CurPage = e.Start / PageSize + 1; //获取当前的页码是多少，也就是第几页
        if (NewOrderStr != (e.Sort + " " + e.Dir) && e.Sort != "")
        {
            NewOrderStr = e.Sort + " " + e.Dir;
        }
        else
        {
            int MaxPage = (e.Total - 1) / PageSize + 1;
            if (MaxPage < PageBar.PageIndex)
            {
                CurPage = MaxPage;
                PageBar.PageIndex = MaxPage;
                return;
            }
        }
        DataTable tmpDT = GetPSData(PageSize, CurPage, NewOrderStr);
        Sto.DataSource = tmpDT;
        Sto.DataBind();
    }

    public virtual DataTable GetPSData(int PageSize, int CurPage, string OrderStr)
    {
        return null;
    }

    #endregion

    /// <summary>
    /// 绑定combox
    /// </summary>
    /// <param name="extcomb"></param>
    /// <param name="defaultValue"></param>
    /// <param name="dropDownKey"></param>
    public void LoadDropDown(Ext.Net.ComboBox extcomb, string defaultValue, string dropDownKey)
    {
        DataTable Tmpdata = CommonLib.BLL.BS_DROPDOWN.GetList("dropdown_name='" + dropDownKey + "'").Tables[0];
        if (Tmpdata.Rows.Count == 0)
            return;
        string TypeV = Tmpdata.Rows[0]["data_content"].ToString();
        string[] arrayStr = TypeV.Split(',');
        extcomb.Items.Clear();
        foreach (var item in arrayStr)
        {
            if (item.Contains(":"))
            {
                string[] kv = item.Split(':');
                extcomb.Items.Add(new ListItem(kv[1], kv[0]));
            }
            else
                extcomb.Items.Add(new ListItem(item, item));
        }
        if (arrayStr.Length == 0)
        {
            extcomb.Items.Add(new ListItem("", ""));
        }
        extcomb.SelectedItem.Text = defaultValue;
    }

    /// <summary>
    /// 名称的OnBlur事件方法，注意默认拼音码的Id是exttxtf_SPELLNO
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void OnNameBlur(object sender, DirectEventArgs e)
    {
        var tf = e.ExtraParams["targetField"];
        if (sender.GetType().FullName == "Ext.Net.ComboBox")
        {

            if (tf != null)
            {
                var s = sender as ComboBox;
                if (!string.IsNullOrWhiteSpace(s.SelectedItem.Text.Trim().ToString()))
                {
                    var txtf = this.FindControl(tf) as TextField;
                    PinWbCode pin = new PinWbCode();
                    txtf.Text = pin.GetPinyin(s.Text.Trim());
                }

            }
        }
        if (sender.GetType().FullName == "Ext.Net.TextField")
        {
            if (tf != null)
            {
                var s = sender as TextField;
                if (!string.IsNullOrWhiteSpace(s.Text.Trim()))
                {
                    var txtf = this.FindControl(tf) as TextField;
                    PinWbCode pin = new PinWbCode();
                    txtf.Text = pin.GetPinyin(s.Text.Trim());
                }

            }
        }
    }

    /// <summary>
    /// 获取父页面ID
    /// </summary>
    /// <returns></returns>
    [DirectMethod]
    public string GetParentTabId()
    {
        return Request.QueryString["ptabid"] + ",Store1";
    }

    /// <summary>
    /// 绑定中心库房，返回查询行数
    /// </summary>
    /// <param name="extCombStore"></param>
    /// <param name="menuNo"></param>
    /// <returns>查询行数</returns>
    public int BindCenterStore(Ext.Net.Store extCombStore, string menuNo)
    {
        string deptIds = CommonLib.BLL.BaseCommonBll.GetDeptIndexList(BS_LoginUserID, menuNo).Trim();
        ViewState["deptIds"] = deptIds;
        DataTable dtStore = CommonLib.BLL.BS_DATASET_SQL.SqlGetTable("select * from VIEW_STORE where STOREROOM_LEVEL='中心库房' and STOREROOM_ID in (" + deptIds + ")");
        extCombStore.DataSource = dtStore;
        extCombStore.DataBind();
        return dtStore.Rows.Count;
    }

    /// <summary>
    /// 生成业务编号，规则：业务前缀+自定义前缀+时间+顺序号
    /// </summary>
    /// <param name="BusinessCode">业务前缀</param>
    /// <param name="tableName">主键表名字</param>
    /// <param name="sub">自定义前缀</param>
    public string GetBusinessNo(string BusinessCode, string tableName, string sub)
    {
        string key = tableName + "_BusinessNo";
        string dtCode = DateTime.Now.ToString("yyMMdd");
        int mid = CommonLib.BLL.CommLibStoreProcedure.PROC_GETNEWSNFORTABLE2(tableName, tableName, tableName, dtCode);
        string No = BusinessCode + sub + dtCode + mid.ToString("000");
        return No;
    }


    #region Grid相关函数

    /// <summary>
    /// 选中行
    /// </summary>
    public void SelectGridRow(GridPanel Grid, String Recordid)
    {
        RowSelectionModel sm = Grid.SelectionModel.Primary as RowSelectionModel;
        sm.SelectedRows.Clear();
        SelectedRow a = new SelectedRow();
        if (Recordid == "")
            a.RowIndex = 0;
        else
            a.RecordID = Recordid;
        sm.SelectedRows.Add(a);
        sm.UpdateSelection();
    }

    public void SelectFristData(GridPanel Grid, DataTable StoreTable, String PKCurrvalue)
    {
        if (StoreTable.Rows.Count == 0)
            return;
        if (PKCurrvalue == "" && !IsPostBack)
        {
            RowSelectionModel sm = Grid.SelectionModel.Primary as RowSelectionModel;
            sm.SelectedRows.Clear();
            SelectedRow a = new SelectedRow();
            if (PKCurrvalue == "")
                a.RowIndex = 0;
            else
                a.RecordID = PKCurrvalue;
            sm.SelectedRows.Add(a);
            sm.UpdateSelection();
        }
    }

    public void SelectFristData(GridPanel Grid, DataTable StoreTable)
    {
        if (StoreTable.Rows.Count == 0)
            return;

        RowSelectionModel sm = Grid.SelectionModel.Primary as RowSelectionModel;
        sm.SelectedRows.Clear();
        SelectedRow a = new SelectedRow();
        a.RowIndex = 0;
        sm.SelectedRows.Add(a);
        sm.UpdateSelection();
    }

    public void SelectedManyRow(GridPanel Grid, int n)
    {
        RowSelectionModel sm = Grid.SelectionModel.Primary as RowSelectionModel;
        if (n >= 0)
        {
            sm.SelectedRows.Add(new SelectedRow(n));
            sm.UpdateSelection();
        }
    }

    public void UnSelectedManyRow(GridPanel Grid, int n)
    {
        RowSelectionModel sm = Grid.SelectionModel.Primary as RowSelectionModel;
        if (n >= 0)
        {
            sm.DeselectRow(n);
            sm.UpdateSelection();
        }
    }
    #endregion


    /// <summary>
    /// Aspose破解方法
    /// </summary>
    public void Crack()//使用前调用一次即可
    {
        string[] stModule = new string[8]
        {
                "\u000E\u2008\u2001\u2000",
                "\u000F\u2008\u2001\u2000",
                "\u0002\u2008\u200B\u2001",
                "\u000F",
                "\u0006",
                "\u000E",
                "\u0003",
                "\u0002"
        };
        Assembly assembly = Assembly.GetAssembly(typeof(Aspose.Cells.License));


        Type typeLic = null, typeIsTrial = null, typeHelper = null;

        foreach (Type type in assembly.GetTypes())
        {
            if ((typeLic == null) && (type.Name == stModule[0]))
            {
                typeLic = type;
            }
            else if ((typeIsTrial == null) && (type.Name == stModule[1]))
            {
                typeIsTrial = type;
            }
            else if ((typeHelper == null) && (type.Name == stModule[2]))
            {
                typeHelper = type;
            }
        }
        if (typeLic == null || typeIsTrial == null || typeHelper == null)
        {
            throw new Exception();
        }
        object lic = Activator.CreateInstance(typeLic);
        int findCount = 0;

        foreach (FieldInfo field in typeLic.GetFields(BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.Instance))
        {
            if (field.FieldType == typeLic && field.Name == stModule[3])
            {
                field.SetValue(null, lic);
                ++findCount;
            }
            else if (field.FieldType == typeof(DateTime) && field.Name == stModule[4])
            {
                field.SetValue(lic, DateTime.MaxValue);
                ++findCount;
            }
            else if (field.FieldType == typeIsTrial && field.Name == stModule[5])
            {
                field.SetValue(lic, 1);
                ++findCount;
            }

        }
        foreach (FieldInfo field in typeHelper.GetFields(BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.Instance))
        {
            if (field.FieldType == typeof(bool) && field.Name == stModule[6])
            {
                field.SetValue(null, false);
                ++findCount;
            }
            if (field.FieldType == typeof(int) && field.Name == stModule[7])
            {
                field.SetValue(null, 128);
                ++findCount;
            }
        }
        if (findCount < 5)
        {
            throw new NotSupportedException("无效的版本");
        }
    }


    #region LINQ结果集转换        
    /// <summary>
    /// LINQ TO Mysql结果转换为DataTable
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="varlist"></param>
    /// <returns></returns>
    public DataTable sqlToDataTable<T>(IQueryable<T> varlist)
    {
        //定义要返回的DataTable对象
        DataTable dtReturn = new DataTable();
        // 保存列集合的属性信息数组
        PropertyInfo[] oProps = null;
        if (varlist == null) return dtReturn;//安全性检查
                                             //循环遍历集合，使用反射获取类型的属性信息
        foreach (T rec in varlist)
        {
            //使用反射获取T类型的属性信息，返回一个PropertyInfo类型的集合
            if (oProps == null)
            {
                oProps = ((Type)rec.GetType()).GetProperties();
                //循环PropertyInfo数组
                foreach (PropertyInfo pi in oProps)
                {
                    Type colType = pi.PropertyType;//得到属性的类型
                                                   //如果属性为泛型类型
                    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
                    == typeof(Nullable<>)))
                    {   //获取泛型类型的参数
                        colType = colType.GetGenericArguments()[0];
                    }
                    //将类型的属性名称与属性类型作为DataTable的列数据
                    dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                }
            }
            //新建一个用于添加到DataTable中的DataRow对象
            DataRow dr = dtReturn.NewRow();
            //循环遍历属性集合
            foreach (PropertyInfo pi in oProps)
            {   //为DataRow中的指定列赋值
                dr[pi.Name] = pi.GetValue(rec, null) == null ?
                    DBNull.Value : pi.GetValue(rec, null);
            }
            //将具有结果值的DataRow添加到DataTable集合中
            dtReturn.Rows.Add(dr);
        }
        return dtReturn;//返回DataTable对象
    }

    /// <summary>
    /// Ling TO DataTable结果转换为DataTable
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="varlist"></param>
    /// <returns></returns>
    public DataTable LLINQToDataTableT<T>(IEnumerable<T> varlist)
    {
        //定义要返回的DataTable对象
        DataTable dtReturn = new DataTable();
        // 保存列集合的属性信息数组
        PropertyInfo[] oProps = null;
        if (varlist == null) return dtReturn;//安全性检查
                                             //循环遍历集合，使用反射获取类型的属性信息
        foreach (T rec in varlist)
        {
            //使用反射获取T类型的属性信息，返回一个PropertyInfo类型的集合
            if (oProps == null)
            {
                oProps = ((Type)rec.GetType()).GetProperties();
                //循环PropertyInfo数组
                foreach (PropertyInfo pi in oProps)
                {
                    Type colType = pi.PropertyType;//得到属性的类型
                                                   //如果属性为泛型类型
                    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
                    == typeof(Nullable<>)))
                    {   //获取泛型类型的参数
                        colType = colType.GetGenericArguments()[0];
                    }
                    //将类型的属性名称与属性类型作为DataTable的列数据
                    dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                }
            }
            //新建一个用于添加到DataTable中的DataRow对象
            DataRow dr = dtReturn.NewRow();
            //循环遍历属性集合
            foreach (PropertyInfo pi in oProps)
            {   //为DataRow中的指定列赋值
                dr[pi.Name] = pi.GetValue(rec, null) == null ?
                    DBNull.Value : pi.GetValue(rec, null);
            }
            //将具有结果值的DataRow添加到DataTable集合中
            dtReturn.Rows.Add(dr);
        }
        return dtReturn;//返回DataTable对象
    }


    /// <summary>
    /// 事例，使用linq对两个datatable根据关联键进行比对，并对主数据集进行修改
    /// </summary>
    /// <param name="dt1">需要返回的DataTable</param>
    /// <param name="dt2">传入比对是需要的数据集</param>
    /// <returns>一个Dataable</returns>
    public DataTable DemoToTable(DataTable dt1, DataTable dt2)
    {
        var Test = from a in dt1.AsEnumerable()
                   join b in dt2.AsEnumerable()
                   on a.Field<string>("Id") equals b.Field<string>("Id")
                    into temp
                   from t in temp.DefaultIfEmpty()
                   select
                   new { ID = (t == null ? a.Field<string>("ID") : t["Score"]), Name = a.Field<string>("Name"), Age = a.Field<string>("Age"), Sex = a.Field<string>("Sex"), Source = t == null ? "" : t["Score"] };
        return LLINQToDataTableT(Test);
    }
    #endregion

    #region Excel导出


    public void CreateExcelOutput(string rptname, string title, DataTable dt, GridPanel grid)
    {
        CreateExcelOutput(rptname, title, dt, grid, true, null);
    }

    public void CreateExcelOutput(string rptname, string title, DataTable dt, GridPanel grid, bool autoSizeColumn, Func<NPOI.HSSF.UserModel.HSSFSheet, int> createSubInfo)
    {
        List<ExcelDataColumn> columns = new List<ExcelDataColumn>();
        foreach (var item in grid.ColumnModel.Columns)
        {
            if (!item.Hidden)
                columns.Add(new ExcelDataColumn(item.Header.Replace("<center>", "").Replace("</center>", ""), item.DataIndex));
        }

        CreateExcelOutput(rptname, title, dt, columns, true, createSubInfo);
    }

    public void CreateExcelOutput(string rptname, string title, DataTable dt, List<ExcelDataColumn> columns, bool autoSizeColumn, Func<NPOI.HSSF.UserModel.HSSFSheet, int> createSubInfo)
    {
        try
        {
            if (dt == null)
            {
                throw new Exception("导出数据内容为Null");
            }
            else
            {
                if (dt.Rows.Count == 0)
                {
                    throw new Exception("导出数据记录为0");
                }
            }

            if (String.IsNullOrEmpty(rptname))
                throw new Exception("导出报表名称为空");

            if (String.IsNullOrEmpty(title))
                throw new Exception("导出标题为空");

            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            string err = WriteSheet(hssfworkbook, rptname, title, dt, columns, autoSizeColumn, createSubInfo);
            if (err == "")
            {
                err = CreateXlsFile(hssfworkbook, rptname);
                if (err == "")
                {
                    downloadfile(rptname);
                }
                else
                {
                    throw new Exception("未能创建导出文件," + err);
                }
            }
            else
            {
                throw new Exception("未能创建导出内容," + err);
            }
        }
        catch (Exception e)
        {
            X.Msg.Alert("系统提示", "导出Execl文件失败！" + e.Message).Show();
        }
    }

    public string WriteSheet(HSSFWorkbook hssfworkbook, string rptname, string title, DataTable dt, List<ExcelDataColumn> columns, bool autoSizeColumn, Func<NPOI.HSSF.UserModel.HSSFSheet, int> createSubInfo)
    {
        try
        {
            int undatarow = 0;
            int rows = 0;
            int lastcellcol = 0;


            //设置边框
            HSSFCellStyle borderstyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
            borderstyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            borderstyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            borderstyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            borderstyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            //创建报表页
            NPOI.HSSF.UserModel.HSSFSheet sheet1 = (NPOI.HSSF.UserModel.HSSFSheet)hssfworkbook.CreateSheet(rptname);

            //标题行，cell（0，0）
            HSSFCell firstcell = (NPOI.HSSF.UserModel.HSSFCell)sheet1.CreateRow(rows).CreateCell(0);
            firstcell.SetCellValue(title);

            HSSFCellStyle style = (NPOI.HSSF.UserModel.HSSFCellStyle)hssfworkbook.CreateCellStyle();
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            HSSFFont font = (NPOI.HSSF.UserModel.HSSFFont)hssfworkbook.CreateFont();
            font.FontHeight = 20 * 20;
            style.SetFont(font);
            style.BorderBottom = style.BorderRight = NPOI.SS.UserModel.BorderStyle.None;
            firstcell.CellStyle = style;
            if (createSubInfo != null)
            {
                int c = createSubInfo(sheet1);
                undatarow += c;
            }

            //标题列
            undatarow += 1;
            rows = undatarow;
            HSSFRow titlerow = (NPOI.HSSF.UserModel.HSSFRow)sheet1.CreateRow(rows);

            int nCol = 0;

            foreach (var clum in columns)
            {
                if (dt.Columns.IndexOf(clum.DataIndex) > -1)
                {
                    dt.Columns[clum.DataIndex].SetOrdinal(nCol);
                    HSSFCell titlecell = (NPOI.HSSF.UserModel.HSSFCell)titlerow.CreateCell(nCol);
                    string cellvalue = clum.ColumnName;
                    titlecell.SetCellValue(cellvalue);
                    titlecell.CellStyle = borderstyle;
                    nCol++;
                }
            }
            /*
            for (int i = 0; i < grid.ColumnModel.Columns.Count; i++)
            {
                if (dt.Columns.IndexOf(grid.ColumnModel.Columns[i].DataIndex) > -1)
                {
                    if (grid.ColumnModel.Columns[i].Hidden == false)
                    {
                        dt.Columns[grid.ColumnModel.Columns[i].DataIndex].SetOrdinal(nCol);
                        HSSFCell titlecell = (NPOI.HSSF.UserModel.HSSFCell)titlerow.CreateCell(nCol);
                        string cellvalue = grid.ColumnModel.Columns[i].Header.Replace("<center>", "").Replace("</center>", "");
                        titlecell.SetCellValue(cellvalue);
                        titlecell.CellStyle = borderstyle;
                        nCol++;
                    }
                    else
                    {
                        dt.Columns.Remove(grid.ColumnModel.Columns[i].DataIndex);
                    }
                }
            }
            */

            //增加标题行样式：合并单元格
            lastcellcol = nCol;
            if ((lastcellcol - 1) < 0)
            {
                lastcellcol = 0;
            }
            else
            {
                lastcellcol = lastcellcol - 1;
            }
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, lastcellcol));

            //删除dt多余列
            nCol = nCol - dt.Columns.Count;
            if (nCol < 0)
            {
                for (int i = nCol; i < 0; i++)
                {
                    dt.Columns.RemoveAt(dt.Columns.Count + i);
                }
            }

            //数据行
            foreach (DataRow dr in dt.Select())
            {
                rows++;
                if (rows < 65535 - undatarow)
                {
                    HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)sheet1.CreateRow(rows);
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.CreateCell(i);

                        switch (dt.Columns[i].DataType.FullName)
                        {
                            case "System.Int32":
                                if (string.IsNullOrEmpty(dr[dt.Columns[i].ColumnName].ToString()))
                                    cell.SetCellValue(0);
                                else
                                    cell.SetCellValue(double.Parse(dr[dt.Columns[i].ColumnName].ToString()));
                                break;
                            case "System.Decimal":
                                if (string.IsNullOrEmpty(dr[dt.Columns[i].ColumnName].ToString()))
                                    cell.SetCellValue(0);
                                else
                                    cell.SetCellValue(double.Parse(dr[dt.Columns[i].ColumnName].ToString()));
                                break;
                            case "System.Double":
                                if (string.IsNullOrEmpty(dr[dt.Columns[i].ColumnName].ToString()))
                                    cell.SetCellValue(0);
                                else
                                    cell.SetCellValue(double.Parse(dr[dt.Columns[i].ColumnName].ToString()));
                                break;
                            case "System.Single":
                                if (string.IsNullOrEmpty(dr[dt.Columns[i].ColumnName].ToString()))
                                    cell.SetCellValue(0);
                                else
                                    cell.SetCellValue(double.Parse(dr[dt.Columns[i].ColumnName].ToString()));
                                break;
                            case "System.Int64":
                                if (string.IsNullOrEmpty(dr[dt.Columns[i].ColumnName].ToString()))
                                    cell.SetCellValue(0);
                                else
                                    cell.SetCellValue(double.Parse(dr[dt.Columns[i].ColumnName].ToString()));
                                break;
                            default:
                                if (string.IsNullOrEmpty(dr[dt.Columns[i].ColumnName].ToString()))
                                    cell.SetCellValue("");
                                else
                                    cell.SetCellValue(dr[dt.Columns[i].ColumnName].ToString());
                                break;
                        }

                        cell.CellStyle = borderstyle;
                    }
                }
            }
            if (autoSizeColumn)
            {
                //列宽自适应
                for (int i = 0; i <= lastcellcol; i++)
                {
                    sheet1.AutoSizeColumn(i);
                }

                //if (dt.Rows.Count > 0)
                //{
                //    int nMax = GetByteLength(dt, 0);
                //    if (nMax > 0)
                //    {
                //        nMax += 2;
                //        sheet1.SetColumnWidth(0, nMax * 256);
                //    }
                //}
            }

            //标题行边框
            sheet1.SetEnclosedBorderOfRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, lastcellcol), NPOI.SS.UserModel.BorderStyle.None, 0);

            return "";
        }
        catch (Exception e)
        {
            return e.Message;
        }

    }

    public string CreateXlsFile(HSSFWorkbook hssfworkbook, string rptname)
    {
        try
        {
            FileStream file = new FileStream(System.Web.HttpContext.Current.Request.PhysicalApplicationPath + "\\TmpFile\\" + rptname + ".xls", FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();
            return "";
        }
        catch (Exception e)
        {
            return e.Message;
        }

    }

    public void downloadfile(string rptname)
    {
        X.Redirect("~/CommonPages/ExcelOutput.aspx?filename=" + HttpUtility.UrlEncode(rptname + ".xls", System.Text.Encoding.UTF8));
    }

    public int GetByteLength(DataTable dt, int col)
    {
        int nLength = 0;
        foreach (DataRow dr in dt.Select())
        {
            byte[] sarr = System.Text.Encoding.Default.GetBytes(dr[col].ToString());

            if (sarr.Length > nLength)
                nLength = sarr.Length;

        }
        return nLength;
    }

    #endregion

}

/// <summary>
/// 导出Excel的列
/// </summary>
public class ExcelDataColumn
{
    public ExcelDataColumn() { }

    public ExcelDataColumn(string _columnName, string _dataIndex)
    {
        ColumnName = _columnName;
        DataIndex = _dataIndex;
    }
    public string ColumnName { get; set; }

    public string DataIndex { get; set; }
}